﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_FIT_REPORT_Fit2013r_Get_Weight_Training_Analysis_Compared_Month')
	BEGIN
		DROP Procedure usp_UPDMS_FIT_REPORT_Fit2013r_Get_Weight_Training_Analysis_Compared_Month
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_FIT_REPORT_Fit2013r_Get_Weight_Training_Analysis_Compared_Month
**	Desc : 월별대비분석표
**	Test Exec Query : Exec usp_UPDMS_FIT_REPORT_Fit2013r_Get_Weight_Training_Analysis_Compared_Month '2014-06', '2014-07', 'en'
**	Called by : Fit_Dac_UPDMS_FIT_WEIGHT_TRAINING.cs
**	Program ID : Fit2013r
**	Auth : 송시명
**	Date : 2014-06-26
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_FIT_REPORT_Fit2013r_Get_Weight_Training_Analysis_Compared_Month]
@ls_pre_month nvarchar(7),
@ls_this_month nvarchar(7),
@ls_lang_set nvarchar(2)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

--결과저장 테이블(중량)
IF OBJECT_ID('tempdb..#TMP_RESULT_WEIGHT') IS NOT NULL
BEGIN
    DROP TABLE #TMP_RESULT_WEIGHT
END

CREATE TABLE #TMP_RESULT_WEIGHT
(
  Training_Part nvarchar(5),
  Training_Part_Nm nvarchar(200), 
  Training_Type_Id nvarchar(5),
  Training_Nm nvarchar(200),
  Pre_Total decimal(10, 2),
  This_Total decimal(10, 2),
  Diff_Total decimal(10, 2)
)

--결과저장 테이블(반복횟수)
IF OBJECT_ID('tempdb..#TMP_RESULT_COUNT') IS NOT NULL
BEGIN
    DROP TABLE #TMP_RESULT_COUNT
END

CREATE TABLE #TMP_RESULT_COUNT
(
  Training_Part nvarchar(5),
  Training_Part_Nm nvarchar(200), 
  Training_Type_Id nvarchar(5),
  Training_Nm nvarchar(200),
  Pre_Total decimal(10, 2),
  This_Total decimal(10, 2),
  Diff_Total decimal(10, 2)
)

DECLARE @sql AS nvarchar(max)
SET @sql= '
--중량별
INSERT #TMP_RESULT_WEIGHT
SELECT Training_Part,
       dbo.ufn_UPDMS_Get_Code_Name(Training_Part, ''FIT'', @ls_lang_set) AS Training_Part_Nm,
       Training_Type_Id,
       Training_Nm,
       ISNULL([' + @ls_pre_month + '],0) AS Pre_Total,
       ISNULL([' + @ls_this_month + '],0) AS This_Total,
       ISNULL([' + @ls_this_month + '],0) - ISNULL([' + @ls_pre_month + '],0) AS Diff_Total
 FROM (
      SELECT LEFT(A.Training_Dt, 7) AS Training_Ym,
             C.Training_Part,
             C.Training_Type_Id,
             C.Training_Nm,
             SUM(B.Action_Weight * B.Action_Count * C.Weight_Calc_Rate * B.Set_Count) AS Total_Weight,
             MAX(C.View_Order) AS View_Order
        FROM UPDMS_FIT_WEIGHT_TRAINING AS A WITH(NOLOCK)
        JOIN UPDMS_FIT_WEIGHT_TRAINING_PARTS AS B WITH(NOLOCK) ON A.Training_Id = B.Training_Id
        JOIN UPDMS_FIT_TRAINING_TYPE AS C WITH(NOLOCK) ON B.Training_Type_Id = C.Training_Type_Id
       WHERE LEFT(A.Training_Dt, 7) IN (''' + @ls_pre_month + ''',''' + @ls_this_month + ''')
       GROUP BY LEFT(A.Training_Dt, 7), Training_Part, C.Training_Type_Id, C.Training_Nm
       ) pv
 PIVOT ( SUM(pv.Total_Weight) FOR pv.Training_Ym IN ([' + @ls_pre_month + '], [' + @ls_this_month + '])
       ) AS pvt
 ORDER BY Training_Part, View_Order

--반복횟수별
INSERT #TMP_RESULT_COUNT
SELECT Training_Part,
       dbo.ufn_UPDMS_Get_Code_Name(Training_Part, ''FIT'', @ls_lang_set) AS Training_Part_Nm,
       Training_Type_Id,
       Training_Nm,
       ISNULL([' + @ls_pre_month + '],0) AS Pre_Total,
       ISNULL([' + @ls_this_month + '],0) AS This_Total,
       ISNULL([' + @ls_this_month + '],0) - ISNULL([' + @ls_pre_month + '],0) AS Diff_Total
 FROM (
      SELECT LEFT(A.Training_Dt, 7) AS Training_Ym,
             C.Training_Part,
             C.Training_Type_Id,
             C.Training_Nm,
             SUM(B.Action_Count * B.Set_Count) AS Total_Count,
             MAX(C.View_Order) AS View_Order
        FROM UPDMS_FIT_WEIGHT_TRAINING AS A WITH(NOLOCK)
        JOIN UPDMS_FIT_WEIGHT_TRAINING_PARTS AS B WITH(NOLOCK) ON A.Training_Id = B.Training_Id
        JOIN UPDMS_FIT_TRAINING_TYPE AS C WITH(NOLOCK) ON B.Training_Type_Id = C.Training_Type_Id
       WHERE LEFT(A.Training_Dt, 7) IN (''' + @ls_pre_month + ''',''' + @ls_this_month + ''')
       GROUP BY LEFT(A.Training_Dt, 7), Training_Part, C.Training_Type_Id, C.Training_Nm
       ) pv
 PIVOT ( SUM(pv.Total_Count) FOR pv.Training_Ym IN ([' + @ls_pre_month + '], [' + @ls_this_month + '])  ) AS pvt
 ORDER BY Training_Part, View_Order
'

DECLARE @param nvarchar(max)
SET @param = '
  @ls_pre_month nvarchar(7),
  @ls_this_month nvarchar(7),
  @ls_lang_set nvarchar(2)
'

--print(@sql)
EXEC sp_executesql @sql, @param,
                   @ls_pre_month = @ls_pre_month,
                   @ls_this_month = @ls_this_month,
                   @ls_lang_set = @ls_lang_set


--결과저장 테이블(반복횟수)
IF OBJECT_ID('tempdb..#TMP_RECENT_WEIGHT_COUNT') IS NOT NULL
BEGIN
    DROP TABLE #TMP_RECENT_WEIGHT_COUNT
END

CREATE TABLE #TMP_RECENT_WEIGHT_COUNT
(
  Training_Id nvarchar(10),
  Training_Type_Id nvarchar(5),
  Total_Weight decimal(10, 2),
  Total_Count decimal(10, 2),
  View_Order nvarchar(5)
)

--최근 운동별 중량, 반복횟수
INSERT #TMP_RECENT_WEIGHT_COUNT
SELECT A.Training_Id,
       C.Training_Type_Id,
       SUM(B.Action_Weight * B.Action_Count * C.Weight_Calc_Rate * B.Set_Count) AS Total_Weight,
       SUM(B.Action_Count * B.Set_Count) AS Total_Count,
       MAX(C.View_Order) AS View_Order
  FROM UPDMS_FIT_WEIGHT_TRAINING AS A WITH(NOLOCK)
  JOIN UPDMS_FIT_WEIGHT_TRAINING_PARTS AS B WITH(NOLOCK) ON A.Training_Id = B.Training_Id
  JOIN UPDMS_FIT_TRAINING_TYPE AS C WITH(NOLOCK) ON B.Training_Type_Id = C.Training_Type_Id
  JOIN (
       SELECT B.Training_Type_Id,
              MAX(A.Training_Id) AS MAX_Training_Id
         FROM UPDMS_FIT_WEIGHT_TRAINING AS A WITH(NOLOCK)
         JOIN UPDMS_FIT_WEIGHT_TRAINING_PARTS AS B WITH(NOLOCK) ON A.Training_Id = B.Training_Id
         JOIN UPDMS_FIT_TRAINING_TYPE AS C WITH(NOLOCK) ON B.Training_Type_Id = C.Training_Type_Id
        GROUP BY B.Training_Type_Id
       ) D
    ON C.Training_Type_Id = D.Training_Type_Id AND A.Training_Id = D.MAX_Training_Id  
 GROUP BY A.Training_Id, C.Training_Type_Id


--최종결과
SELECT A.Training_Part,
       A.Training_Part_Nm,
       A.Training_Type_Id,
       A.Training_Nm,
       A.Pre_Total AS Weight_Pre_Total,
       A.This_Total AS Weight_This_Total,
	   A.Diff_Total AS Weight_Diff_Total,
       CASE WHEN A.Diff_Total < 0 THEN
            CASE WHEN C.Total_Weight > 0 THEN
                 CONVERT(decimal(10, 1), (A.Diff_Total / C.Total_Weight ) * -1)
                 ELSE 0
            END
            ELSE 0
       END Weight_Remain_Day,
	   A.This_Total / (CASE WHEN A.Pre_Total = 0 THEN 1 ELSE A.Pre_Total END) * 100 AS Weight_Avg,
       B.Pre_Total AS Count_Pre_Total,
       B.This_Total AS Count_This_Total,
	   B.Diff_Total AS Count_Diff_Total,
       CASE WHEN B.Diff_Total < 0 THEN
            CASE WHEN C.Total_Count > 0 THEN
                 CONVERT(decimal(10, 1), (B.Diff_Total / C.Total_Count ) * -1)
                 ELSE 0
            END
            ELSE 0
       END Count_Remain_Day,
	   B.This_Total / (CASE WHEN B.Pre_Total = 0 THEN 1 ELSE B.Pre_Total END) * 100 AS Count_Avg,
       C.Total_Weight AS Weight_Recent_Total,
       C.Total_Count AS Count_Recent_Total,
       C.View_Order
  FROM #TMP_RESULT_WEIGHT A
  JOIN #TMP_RESULT_COUNT B ON A.Training_Part = B.Training_Part AND A.Training_Type_Id = B.Training_Type_Id
  JOIN #TMP_RECENT_WEIGHT_COUNT C ON A.Training_Type_Id = C.Training_Type_Id
--소계 
UNION ALL
SELECT A.Training_Part,
       A.Training_Part_Nm,
       'BA001' AS Training_Type_Id,
       dbo.ufn_UPDMS_Get_Code_Name('BA001', 'FIT', @ls_lang_set) AS Training_Nm,
       SUM(A.Pre_Total) AS Weight_Pre_Total,
       SUM(A.This_Total) AS Weight_This_Total,
       SUM(A.Diff_Total) AS Weight_Diff_Total,
       CASE WHEN SUM(A.Diff_Total) < 0 THEN
            CASE WHEN SUM(C.Total_Weight) > 0 THEN
                 CONVERT(decimal(10, 1), (SUM(A.Diff_Total) / SUM(C.Total_Weight) ) * -1)
                 ELSE 0
            END
            ELSE 0
       END Weight_Remain_Day,
       SUM(A.This_Total) / (CASE WHEN SUM(A.Pre_Total) = 0 THEN 1 ELSE SUM(A.Pre_Total) END) * 100 AS Weight_Avg,
	   SUM(B.Pre_Total) AS Count_Pre_Total,
       SUM(B.This_Total) AS Count_This_Total,
	   SUM(B.Diff_Total) AS Count_Diff_Total,
       CASE WHEN SUM(B.Diff_Total) < 0 THEN
            CASE WHEN SUM(C.Total_Count) > 0 THEN
                 CONVERT(decimal(10, 1), (SUM(B.Diff_Total) / SUM(C.Total_Count) ) * -1)
                 ELSE 0
            END
            ELSE 0
       END Count_Remain_Day,
       SUM(B.This_Total) / (CASE WHEN SUM(B.Pre_Total) = 0 THEN 1 ELSE SUM(B.Pre_Total) END) * 100 AS Count_Avg,
       SUM(C.Total_Weight) AS Weight_Recent_Total,
       SUM(C.Total_Count) AS Count_Recent_Total,
       'BA001' View_Order
  FROM #TMP_RESULT_WEIGHT A
  JOIN #TMP_RESULT_COUNT B ON A.Training_Part = B.Training_Part AND A.Training_Type_Id = B.Training_Type_Id
  JOIN #TMP_RECENT_WEIGHT_COUNT C ON A.Training_Type_Id = C.Training_Type_Id
 GROUP BY A.Training_Part, A.Training_Part_Nm
--총계 
UNION ALL
SELECT 'BA002' AS Training_Part,
       dbo.ufn_UPDMS_Get_Code_Name('BA002', 'FIT', @ls_lang_set) AS Training_Part_Nm,
       'BA002' AS Training_Type_Id,
       '' AS Training_Nm,
       SUM(A.Pre_Total) AS Weight_Pre_Total,
       SUM(A.This_Total) AS Weight_This_Total,
       SUM(A.Diff_Total) AS Weight_Diff_Total,
       CASE WHEN SUM(A.Diff_Total) < 0 THEN
            CASE WHEN SUM(C.Total_Weight) > 0 THEN
                 CONVERT(decimal(10, 1), (SUM(A.Diff_Total) / SUM(C.Total_Weight) ) * -1)
                 ELSE 0
            END
            ELSE 0
       END Weight_Remain_Day,
       SUM(A.This_Total) / (CASE WHEN SUM(A.Pre_Total) = 0 THEN 1 ELSE SUM(A.Pre_Total) END) * 100 AS Weight_Avg,
       SUM(B.Pre_Total) AS Count_Pre_Total,
       SUM(B.This_Total) AS Count_This_Total,
       SUM(B.Diff_Total) AS Count_Diff_Total,
       CASE WHEN SUM(B.Diff_Total) < 0 THEN
            CASE WHEN SUM(C.Total_Count) > 0 THEN
                 CONVERT(decimal(10, 1), (SUM(B.Diff_Total) / SUM(C.Total_Count) ) * -1)
                 ELSE 0
            END
            ELSE 0
       END Count_Remain_Day,
	   SUM(B.This_Total) / (CASE WHEN SUM(B.Pre_Total) = 0 THEN 1 ELSE SUM(B.Pre_Total) END) * 100 AS Count_Avg,
       SUM(C.Total_Weight) AS Weight_Recent_Total,
       SUM(C.Total_Count) AS Count_Recent_Total,
       'BA002' View_Order
  FROM #TMP_RESULT_WEIGHT A
  JOIN #TMP_RESULT_COUNT B ON A.Training_Part = B.Training_Part AND A.Training_Type_Id = B.Training_Type_Id
  JOIN #TMP_RECENT_WEIGHT_COUNT C ON A.Training_Type_Id = C.Training_Type_Id
 ORDER BY A.Training_Part, C.View_Order

GO